package net.comm.group.db;

import org.sakaiproject.db.api.iServiceSql;


/**
 * methods for accessing user data in a database.
 */
public class GdianpingServiceSqlMysql implements iServiceSql
{
	/**
	 * 返回根据主键删除语句
	 */
	public String getDeleteSql()
	{
		return "delete from Gdianping where seqID=? ";
	}

	/**
	 * 返回根据主键删除语句
	 */
	public String getDelbydsortIDSql()
	{
		return "delete from Gdianping where dsort=?  and dsortID=?";
	}

	/**
	 * 返回插入语句
	 */
	public String getInsertSql()
	{
		return "insert into Gdianping ( seqID,  groupID, dsort,  dsortID, dtype, dcont,  userID,  mtime " +
				") values (?,?,?,?,?,?,?,?)";
	}
	
	/**
	 * 返回根据主键修改语句
	 */
	public String getUpdateSql()
	{
		return "update Gdianping  set  groupID=?, dsort=?, dsortID=?, dtype=?, dcont=?,  userID=?,  mtime=? " +  
				" where seqID=? ";
	}

	/**
	 * 返回根据主键查询语句
	 */
	public String getbykeySql()
	{
		return "select seqID,  groupID, dsort,  dsortID, dtype, dcont,  userID,  mtime " +
				" from Gdianping where  seqID=? ";
	}
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyPageSql()
	{
	
		StringBuffer sb =  new StringBuffer("select  seqID, groupID,  dsort,  dsortID, dtype, dcont,  userID,  mtime " +
				" from Gdianping ");
		
		sb.append(" where dsort=? and dsortID=? ");
		sb.append(" limit ?, ? ");
				
		return sb.toString();	
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecbyDsortUserIDSql()
	{
	
		StringBuffer sb =  new StringBuffer("select  seqID, groupID,  dsort,  dsortID, dtype, dcont,  userID,  mtime " +
				" from Gdianping ");
		
		sb.append(" where dsort=? and dsortID=? and userID=? ");
		sb.append(" limit 1 ");
				
		return sb.toString();	
	}	
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecSql()
	{
	
		StringBuffer sb =  new StringBuffer("select  seqID,  groupID, dsort,  dsortID, dtype, dcont,  userID,  mtime " +
				" from Gdianping ");
		
		return sb.toString();	
	}	
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getGroupPicSql()
	{
	
		StringBuffer sb =  new StringBuffer("select g.dsort, g.dtype, count(g.dtype) as DtypeSum, " +
				" g.userID, u.Nick, u.PicID " +
				" from Gdianping g left join Userdetails u on g.userID = u.userID " +
				" where g.dsort=2 and g.DsortID in (select p.GpicID from GPicture p " +
				"where p.GalbumID in (select a.GalbumID from GAlbum a where GroupID = ? ))" +
				" Group by g.userID, g.dtype order by g.userID, DtypeSum desc, g.dtype  limit ? ");
		
		return sb.toString();
	}	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getGroupPartySql()
	{
	
		StringBuffer sb =  new StringBuffer("select g.dsort, g.dtype, count(g.dtype) as DtypeSum, " +
				" g.userID, u.Nick, u.PicID " +
				" from Gdianping g left join Userdetails u on g.userID = u.userID " +
				" where g.dsort=3 and g.DsortID in (select p.PartyID from Gparty p " +
				"where p.GroupID = ? )" +
				" Group by g.userID, g.dtype order by DtypeSum desc, g.dtype  limit ? ");
		
		return sb.toString();	
	}	
	
	/**
	 * return the sql statement which updates an external user id for a given user in the sakai_user_id_map table.
	 */
	public String getRecBbyDsortIDSql()
	{
	
		StringBuffer sb =  new StringBuffer("select groupID, dsort, dsortID, dtype, " +
				"count(dtype) as DtypeSum " +
				" from Gdianping " + 
				"where dsort = ? and dsortID=? Group by dtype ");
		
		return sb.toString();	
	}	
	
	/**
	 * 返回所有记录数
	 */
	public String getCountSql1()
	{
		return "select count(*) as COUNT from Gdianping ";
	}


	/**
	 * 返回根据查询条件的记录数
	 */
	public String getCountSql2()
	{
		return "select count(*) as COUNT from Gdianping where UserID=? ";
	}

	/**
	 * 返回所有记录数
	 */
	public String getCountSql3()
	{
		return "select count(*) as COUNT from Gdianping where Dsort=?  and DsortID=?";
	}

	
	/**
	 * @see net.comm.job.db.iServiceSql.impl.UserServiceSql#getMaxInputsForSelectWhereInQueries()
	 */
	public int getMaxInputsForSelectWhereInQueries() {
		// For Oracle, the maximum supported number of expressions in a list is 1000. 
		return 1000;
	}
}
